/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Database;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.io.*;
import java.util.*;
import Common.Log;
import Common.XMLParser;
import Core.*;


/**
 *
 * @author harish
 */
public class Database
{
    private static String _url="";
    private static String _user="";
    private static String _pwd="";
    private static Connection _con;
    private static Database _instance =null;
    private Database(){}  
    

    public static Database GetInstance()
    {
        if(_instance==null)
        {
            _instance=new Database();
        }        
        return _instance;
    }

    public void Configure()
    {
        try
        {
            _url=Configuration.GetInstance().GetURL();
            _user=Configuration.GetInstance().GetUser();
            _pwd=Configuration.GetInstance().GetPwd();
            _con = DriverManager.getConnection(_url,_user,_pwd);           
            Log.GetInstance().Write("DB Conection succeeded");          
        }
        catch (SQLException ex)
        {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
        catch(IOException e)
        {
            try
            {
            Log.GetInstance().Write("Error in DB Connection");
            }
            catch(IOException i)
            {

            }
        }
        finally
        {
            try
            {
                if (_con != null)
                {
                    _con.close();
                }
            }
            catch (SQLException ex)
            {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
    
    public void InsertHome(int configId)
    {
        try
        {
             _con = DriverManager.getConnection(_url,_user,_pwd);
            Statement st = _con.createStatement();
            st.executeUpdate("INSERT INTO tHome(idConfiguration) values(" + configId + ")");
        }
        catch (SQLException ex)
        {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    public void InsertConfiguration(String s)
    {
        try
        {
             _con = DriverManager.getConnection(_url,_user,_pwd);
            PreparedStatement st = _con.prepareStatement("INSERT INTO tConfiguration(ConfigurationXML) values('" + s.replace("\\", "\\\\") + "')");
            st.executeUpdate();
        }
        catch (SQLException ex)
        {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    public void InsertAllocation(int homeId,int applianceId)
    {
        try
        {
             _con = DriverManager.getConnection(_url,_user,_pwd);
            PreparedStatement st = _con.prepareStatement("INSERT INTO tAllocation(HomeId,ApplianceId) values(" + homeId + "," + applianceId + ")");
            st.executeUpdate();
        }
        catch (SQLException ex)
        {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    public void InsertData1(List<Data> data)
    {
         try
        {
             _con = DriverManager.getConnection(_url,_user,_pwd);
             _con.setAutoCommit(false);
            PreparedStatement st = _con.prepareStatement("INSERT INTO tData(idHome,idAppliance,idPolicy,start_time,end_time) values(?,?,?,?,?)");
            for(Data d:data)
            {
                st.setInt(1,d.homeId );
                st.setInt(2,d.applianceId);
                st.setInt(3,d.policyId);
                st.setDate(4, new java.sql.Date(d.start_time.getTime().getTime()));
                st.setDate(5, new java.sql.Date(d.end_time.getTime().getTime()));
                st.addBatch();
            }
            st.executeBatch();
            _con.commit();
        }
        catch (SQLException ex)
        {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    public void InsertData(List<Data> data)
    {
         try
        {
             _con = DriverManager.getConnection(_url,_user,_pwd);
             _con.setAutoCommit(false);
            PreparedStatement st = _con.prepareStatement("INSERT INTO tData(idHome,idAppliance,start_time,end_time) values(?,?,?,?)");
            for(Data d:data)
            {
                st.setInt(1,d.homeId );
                st.setInt(2,d.applianceId);
                //st.setInt(3,d.policyId);
                st.setDate(3, new java.sql.Date(d.start_time.getTime().getTime()));
                st.setDate(4, new java.sql.Date(d.end_time.getTime().getTime()));
                st.addBatch();
            }
            st.executeBatch();
            _con.commit();
        }
        catch (SQLException ex)
        {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
    
    public int ReadCurrentConfiguration()
    {
        ResultSet rs=null;
        PreparedStatement pst=null;
        try 
        {   
             _con = DriverManager.getConnection(_url,_user,_pwd);
            pst = _con.prepareStatement("SELECT MAX(idtConfiguration) FROM tConfiguration");
            rs = pst.executeQuery();
            rs.next();
            return rs.getInt(1);                

        } 
        catch (SQLException ex) 
        {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
                return 0;
        } 
        finally 
        {
            try 
            {
                if (rs != null) 
                {
                    rs.close();
                }
                if (pst != null) 
                {
                    pst.close();
                }
                if (_con != null) 
                {
                    _con.close();
                }
            } 
            catch (SQLException ex) 
            {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
    
    public int ReadCurrentHome()
    {
        ResultSet rs=null;
        PreparedStatement pst=null;
        try 
        {   
             _con = DriverManager.getConnection(_url,_user,_pwd);
            pst = _con.prepareStatement("SELECT MAX(idtHome) FROM tHome");
            rs = pst.executeQuery();
            rs.next();
            return rs.getInt(1);                

        } 
        catch (SQLException ex) 
        {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
                return 0;
        } 
        finally 
        {
            try 
            {
                if (rs != null) 
                {
                    rs.close();
                }
                if (pst != null) 
                {
                    pst.close();
                }
                if (_con != null) 
                {
                    _con.close();
                }
            } 
            catch (SQLException ex) 
            {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
    
    public String ReadCurrentConfigAsString(int ConfigurationId)
    {
        
        ResultSet rs=null;
        PreparedStatement pst=null;
        try 
        {   
             _con = DriverManager.getConnection(_url,_user,_pwd);
            pst = _con.prepareStatement("SELECT * FROM tConfiguration WHERE idtConfiguration=" + ConfigurationId);
            rs = pst.executeQuery();
            rs.next();
            return rs.getString(2);                

        } 
        catch (Exception ex) 
        {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
                return "";
        } 
        finally 
        {
            try 
            {
                if (rs != null) 
                {
                    rs.close();
                }
                if (pst != null) 
                {
                    pst.close();
                }
                if (_con != null) 
                {
                    _con.close();
                }
            } 
            catch (SQLException ex) 
            {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            } 
        }
    }
    
    public List<Appliance> ReadNonEssentialAppliances()
    {
        ResultSet rs=null;
        List<Appliance> ls=new ArrayList<Appliance>();
        PreparedStatement pst=null;
        try 
        {       
             _con = DriverManager.getConnection(_url,_user,_pwd);
            pst = _con.prepareStatement("SELECT * FROM tAppliance WHERE IsEssential=0");
            rs = pst.executeQuery();
            while(rs.next())
            {
                Appliance a=new Appliance();
                a._applianceId=rs.getInt("idtAppliance");
                a._applianceName=rs.getString("nameAppliance");
                a._powerConsumed=rs.getInt("Consumption");
                ls.add(a);
            }
            return ls;              

        } 
        catch (SQLException ex) 
        {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
                return null;
        } 
        finally 
        {
            try 
            {
                if (rs != null) 
                {
                    rs.close();
                }
                if (pst != null) 
                {
                    pst.close();
                }
                if (_con != null) 
                {
                    _con.close();
                }
            } 
            catch (SQLException ex) 
            {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }    
    
    public List<Appliance> ReadEssentialAppliances()
    {
        ResultSet rs=null;
        PreparedStatement pst=null;
        List<Appliance> ls=new ArrayList<Appliance>();
        try 
        {       
             _con = DriverManager.getConnection(_url,_user,_pwd);
            pst = _con.prepareStatement("SELECT * FROM tAppliance WHERE IsEssential=1");
            rs = pst.executeQuery();  
            while(rs.next())
            {
                Appliance a=new Appliance();
                a._applianceId=rs.getInt("idtAppliance");
                a._applianceName=rs.getString("nameAppliance");
                a._powerConsumed=rs.getInt("Consumption");                
                ls.add(a);
            }
            return ls;
        } 
        catch (SQLException ex) 
        {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
                return null;
        } 
        finally 
        {
            try 
            {
                if (rs != null) 
                {
                    rs.close();
                }
                if (pst != null) 
                {
                    pst.close();
                }
                if (_con != null) 
                {
                    _con.close();
                }
            } 
            catch (SQLException ex) 
            {
                Logger lgr = Logger.getLogger(Database.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
